from itertools import groupby

from odoo import api, fields, models, _
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError
from ...sb_sale.models.sale_order import SALE_ORDER_STATE


class SaleOrderTrackingReport(models.Model):
    _name = 'sale.order.tracking.report'
    _description = "Sale Order Tracking Report"
    _auto = False

    #  order_line_id = fields.Many2one('sale.order.line')
    order_id = fields.Many2one('sale.order')
    order_line_id = fields.Many2one('sale.order.line', 'line_id')
    # MO
    sale_name = fields.Char(string='MO')
    product_id = fields.Many2one('product.product', string='Product')
    # 订单状态
    sale_type = fields.Char(string='Approval Status')
    sale_state = fields.Selection([('hold', 'Hold'), ('valid', 'Valid')], string='Order Status', compute="_get_sale_state")

    def _get_sale_state(self):
        for row in self:
            row.sale_state = 'hold' if row.order_id.status == 'hold' else 'valid'

    eng_state = fields.Selection([('hold', 'Hold'), ('valid', 'Valide')], string="Eng Status", compute="_get_eng_state")

    def _get_eng_state(self):
        for row in self:
            row.eng_state = 'valid'

    state = fields.Selection(SALE_ORDER_STATE, string='State', related="order_id.state")
    # 生产型号
    default_code = fields.Char(related='product_id.default_code', string='Product-MPN')
    # 客户型号
    pn = fields.Char(string='PN')
    pn_rev = fields.Char(string="PN REV.")
    # 投料数
    sale_product_qty = fields.Float(string='Product Qty', compute='_compute_sale_product_qty', digits=(16, 0))

    def _compute_sale_product_qty(self):
        for rec in self:
            rec.sale_product_qty = sum(rec.order_id.mrp_production_ids.mapped('product_qty'))

    # 补投数量
    cz_qty = fields.Float(string='Remark Qty', compute='_compute_cz_qty')

    def _compute_cz_qty(self):
        for rec in self:
            rec.cz_qty = sum(rec.order_id.mrp_production_ids.mapped('sb_child_cz_qty'))

    # 补投时间
    cz_date = fields.Date(string='Remark Date', compute='_compute_cz_date')

    def _compute_cz_date(self):
        for rec in self:
            if rec.order_id.mrp_production_ids and rec.order_id.mrp_production_ids.mapped('sb_child_cz_ids'):
                rec.cz_date = min(rec.order_id.mrp_production_ids.mapped('sb_child_cz_ids').mapped('create_date'))
            else:
                rec.cz_date = None

    # 产品版本号
    versions = fields.Char(string='Versions')
    # 厂别
    manufacture = fields.Char(string='Manufacture')
    # 订单类型
    order_type = fields.Selection([
        ('new', _('N')),
        ('repeat', _('R')),
        ('change', _('CHANGE')),
        ('development', _('R&D'))
    ], string="Order Type")
    # 客户代码
    customer_code = fields.Char(string='Code')
    # 跟单员
    user_name = fields.Char(string='Tracking User')
    # 客户订单号
    po = fields.Char(string='PO')
    # 客户名称
    customer_name = fields.Char(string='Customer Name')
    # 下单日期
    order_create_date = fields.Date(string='Order Date')
    # 计划交期
    planned_receiving_date = fields.Date(string='Planed Date')
    # 订单数（PCS）
    product_uom_qty = fields.Float(string='Product Uom Qty', digits=(16, 0))
    # 已交数（PCS）
    qty_delivered = fields.Float(string='Delivered Qty', digits=(16, 0))
    # 未交数（PCS）
    undelivery_qty = fields.Float(string='Undelivered Qty', compute='_compute_qty_undelivered', digits=(16, 0))
    # 订单面积（m2）
    product_metre = fields.Float(string='Order Area(㎡)', compute="_compute_product_metre", digits=(16, 6))
    # 未交货面积（m2）
    undelivery_metre = fields.Float(string='Undelivered Area(㎡)', compute="_compute_product_metre", digits=(16, 6))
    # 库存面积（m2）
    stock_metre = fields.Float(string="Stock Area(㎡)", compute="_compute_product_metre", digits=(16, 6))

    available_quantity = fields.Float(string='Stock QtY', related='product_id.qty_available', digits=(16, 0))
    # 市场备注
    note = fields.Html(string='Note', related='order_id.note')
    # 特殊订单类型
    special_order_type = fields.Selection([
        ('null', 'Null'),
        ('eq_only', 'EQ only'),
        ('free_cost', 'Free cost'),
        ('in_stock', 'In stock'),
        ('wb', 'WB'),
        ('prepare_materials', 'Prepare Materials'),
    ], string="Special Order Type", default='null', related="order_id.special_order_type")
    end_po = fields.Char(string="End PO", related="order_id.end_po")
    end_pn = fields.Char(string="End PN", related="order_id.end_pn")
    end_pn_rev = fields.Char(string="End PN REV", related="order_id.end_pn_rev")
    incoming_qty = fields.Float(compute='_compute_incoming_qty', string="Incoming Qty", digits=(16, 0))
    order_qty = fields.Integer('Order Qty', compute='_compute_order_qty', digits=(16, 0))
    planned_qty = fields.Integer('Planned Qty', compute="_get_planned_qty")

    def _get_planned_qty(self):
        for row in self:
            row.planned_qty = sum(row.order_line_id.s_mrp_production_ids.filtered(lambda x: x.origin == row.order_id.name).move_finished_ids.mapped(lambda x: x.product_uom_qty))

    scrap_qty = fields.Integer('Scrap Qty', compute="_get_scrap_qty")

    def _get_scrap_qty(self):
        for row in self:
            row.scrap_qty = sum(row.order_line_id.s_mrp_production_ids.filtered(lambda x: x.origin == row.order_id.name).mapped(lambda x: x.qty_reject))

    price_unit = fields.Float('Unit Price', related="order_line_id.price_unit")
    nre = fields.Float('NRE', compute="_get_nre")

    def _get_nre(self):
        for row in self:
            row.nre = sum(row.order_id.order_line.filtered(lambda x: x.product_id.name == 'NRE').mapped('price_unit'))

    l_currency_id = fields.Many2one(comodel_name='res.currency', related="order_line_id.currency_id")
    amount_total = fields.Monetary(string="Total", related="order_id.amount_total")
    currency_id = fields.Many2one(comodel_name='res.currency', related="order_id.currency_id")
    short_name = fields.Char(string="Short name")
    #  short_name = fields.Char(string="Short name", related="order_id.partner_id.short_name", compute='_compute_field_values')
    commitment_date = fields.Datetime(string='Customer Date', related="order_id.commitment_date")
    final_delivery_date = fields.Date(string='Final DD')
    user_id = fields.Many2one(comodel_name='res.users', string="Salesperson")
    #  currency_rate = fields.Float( string="Currency Rate", digits=0, related="order_id.currency_rate")
    sb_workflow_state = fields.Selection(related="order_id.sb_workflow_state", readonly=True, string="Approval Status")
    sb_wf_state = fields.Char('Workflow State', compute="_get_sb_wf_state")
    wip_qty = fields.Float('Wip Qty', compute="_get_wip_qty", digits=(16, 0))
    wip_state = fields.Char('Production Status', compute="_get_wip_state")
    wip_state_cn = fields.Char('Production Status', compute="_get_wip_state")
    terms_of_trade = fields.Char('Incoterm', compute="_get_terms_of_trade")
    delivery_status = fields.Selection(
        [
            ('pending', 'None'),
            ('started', 'Started'),
            ('cancel', 'Cancel'),
            ('partial', 'Partial'),
            ('full', 'Fully Delivered'),
        ]
        , string='Delivery Status', store=False, required=False, related=False, search='_search_delivery_status',
        compute="_compute_delivery_status")

    @api.depends('order_id.delivery_status')
    def _compute_delivery_status(self):
        for row in self:
            if row.order_id.delivery_status == 'pending':
                row.delivery_status = row.order_id.delivery_status
            elif row.order_id.delivery_status == 'full':
                row.delivery_status = row.order_id.delivery_status
            elif row.order_id.delivery_status == 'started':
                row.delivery_status = row.order_id.delivery_status
            elif row.order_id.delivery_status == 'cancel':
                row.delivery_status = row.order_id.delivery_status
            elif row.order_id.delivery_status == 'partial':
                row.delivery_status = row.order_id.delivery_status
            else:
                row.delivery_status = False

    def _search_delivery_status(self, operator, value):
        rows = self.env['sale.order'].search([])
        domain = [('order_id.delivery_status', operator, value)]
        return domain

    def _get_terms_of_trade(self):
        for row in self:
            row.terms_of_trade = row.order_id.incoterm.code

    def _get_wip_qty(self):
        for row in self:
            row.wip_qty = sum(row.order_line_id.s_mrp_production_ids.filtered(lambda x: x.origin == row.order_id.name).move_finished_ids.mapped(lambda x: x.quantity))
            # if row == min(self.filtered(lambda x:x.product_id==row.product_id)):
            #    row.wip_qty = sum(self.env['mrp.workorder.sum'].search([(
            #            'product_id','=',row.product_id.id
            #        )]).mapped(lambda x:x.qty_wip))
            # else:
            #    row.wip_qty = 0

    def _get_wip_state(self):
        for row in self:
            workorder = self.env['mrp.workorder.sum'].search([('product_id', '=', row.product_id.id)]).mapped(lambda x: (x.work_station_code, x.qty_wip))
            row.wip_state = '/'.join([x[0] + ':' + str(int(x[1])) for x in workorder])
            workorder = self.env['mrp.workorder.sum'].search([('product_id', '=', row.product_id.id)]).mapped(lambda x: (x.work_station_id.name, x.qty_wip))
            row.wip_state_cn = '/'.join([x[0] + ':' + str(int(x[1])) for x in workorder])

    def _get_sb_wf_state(self):
        WF_I18N_TEXT = {
            'approval': _('Approval'),
            'approve': _('Approved'),
            'PMC': _('PMC'),
            'ENG': _('ENG')
        }
        for row in self:
            if row.order_id.sb_workflow_state == 'approval':
                if row.order_id.sb_workflow_id.sb_workflow_item_ids.filtered(lambda x: x.state == 'approval').is_plan:
                    state = 'PMC'
                else:
                    state = 'ENG'
            else:
                #  state = dict(row.order_id.sb_workflow_id._fields['state'].selection).get(row.sb_workflow_state)
                state = row.sb_workflow_state
            row.sb_wf_state = WF_I18N_TEXT.get(state)

    def _compute_product_metre(self):
        for o_row in self:
            size = 0
            undelivery_size = 0
            stocked_size = 0
            for row in o_row.order_id.order_line:
                PCSW = 0
                PCSL = 0
                if row.product_id.uom_value_ids:
                    PCSW = row.product_id.uom_value_ids.filtered(lambda x: x.uom_id.code == 'PCSW')[-1:].uom_value
                    PCSL = row.product_id.uom_value_ids.filtered(lambda x: x.uom_id.code == 'PCSL')[-1:].uom_value
                if PCSW and PCSL:
                    size += o_row.order_qty * ((PCSW * PCSL) / 1000000)
                    undelivery_size += o_row.undelivery_qty * ((PCSW * PCSL) / 1000000)
                    stocked_size += o_row.available_quantity * ((PCSW * PCSL) / 1000000)
                else:
                    size += o_row.order_qty * row.product_id.uom_value_ids.filtered(lambda x: x.uom_id.code == 'PCSA').uom_value
                    undelivery_size += o_row.undelivery_qty * row.product_id.uom_value_ids.filtered(lambda x: x.uom_id.code == 'PCSA').uom_value
                    stocked_size += o_row.available_quantity * row.product_id.uom_value_ids.filtered(lambda x: x.uom_id.code == 'PCSA').uom_value
            o_row.product_metre = size
            o_row.undelivery_metre = undelivery_size
            o_row.stock_metre = stocked_size

    def _compute_qty_undelivered(self):
        for order in self:
            order.undelivery_qty = order.order_qty - order.qty_delivered

    @api.depends('order_id')
    def _compute_display_name(self):
        result = []
        for record in self:
            display_name = f"{record.sale_name}"
            record.display_name = display_name

    @api.depends('order_id')
    def _compute_order_qty(self):
        for row in self:
            row.order_qty = sum(row.order_id.order_line.filtered(lambda x: x.product_id.detailed_type != 'service').mapped('product_uom_qty'))

    def _compute_incoming_qty(self):
        for row in self:
            row.incoming_qty = sum(row.order_id.product_template_ids.mapped('incoming_qty'))

    @property
    def _table_query(self):
        #  start_date = self._context.get('start_date') or ''
        #  end_date = self._context.get('end_date') or ''
        #  where = ''
        #  if start_date and end_date:
        #  where += f" AND sml.DATE >= '{start_date}' AND sml.DATE <= '{end_date}'"
        sql = f"""
            SELECT 
                so.ID AS id,
                so.ID AS order_id,
                max(sol.id) AS order_line_id,
                max(sol.product_id) AS product_id,
                so.NAME AS sale_name,--销售单号MO
                case when so.state in ('draft','sent') then 'S'
                     when so.state in ('sale') then 'P'
                     else 'C' end AS sale_type,--订单类型
                so.state AS state,--订单状态
              max(rp.short_name) AS short_name,--客户名称
              so.user_id AS user_id,--销售员
                max(sol.pn) AS pn,--客户型号
                max(sol.pn_rev) as pn_rev,
                max(pt.versions) AS versions,--产品版本号
                'WTT' AS manufacture,--厂别
                so.order_type AS order_type,--订单类型
                max(rp.code) AS customer_code,--客户代码
                max(rp2.name) AS user_name,--跟单员
                so.po AS po,--客户订单号
                max(rp.NAME) AS customer_name,--客户名称
                so.create_date AS order_create_date,--下单日期
                so.planned_receiving_date AS planned_receiving_date,--计划交期
                so.final_delivery_date AS final_delivery_date,
                sum(sol.product_uom_qty) AS product_uom_qty,--订单数（PCS）
                sum(sol.qty_delivered) AS qty_delivered--已交数（PCS）
            FROM sale_order so
                LEFT JOIN sale_order_line sol ON sol.order_id = so.ID 
              LEFT JOIN product_product pp ON pp.ID = sol.product_id
                LEFT JOIN product_template pt ON pt.ID = pp.product_tmpl_id
                LEFT JOIN res_partner rp ON rp.ID = so.partner_id
                LEFT JOIN res_users ru ON ru.ID = so.user_id
                LEFT JOIN res_partner rp2 ON rp2.ID = ru.partner_id
            WHERE
                so.STATE not in ('cancel')
            group by so.id
        """
        return sql
